Imports System.Data.SqlClient
Imports Microsoft.VisualBasic

Public Class FixCase
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents btnReturn As System.Web.UI.WebControls.Button

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
    End Sub

    Private Sub FixUsernameCase()

        Dim strsql As String
        Dim sqlReaderTo As SqlDataReader

        Dim objConn As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings.Item("SQLConnectionString"))
        Dim cmdGetEmail As SqlCommand

        strsql = "select * from members where paid = 'Y'"

        cmdGetEmail = New SqlCommand(strsql, objConn)
        cmdGetEmail.CommandType = CommandType.Text
        objConn.Open()
        sqlReaderTo = cmdGetEmail.ExecuteReader(CommandBehavior.CloseConnection)

        While sqlReaderTo.Read()
            'strarray += StrConv(sqlReaderTo("firstname").ToString, VbStrConv.ProperCase)
            'strarray += " "
            'strarray += StrConv(sqlReaderTo("lastname").ToString, VbStrConv.ProperCase)
            'strarray += "<br />"
            SaveUserProperCase(sqlReaderTo("member_id").ToString, _
                StrConv(sqlReaderTo("FirstName").ToString, VbStrConv.ProperCase), _
                StrConv(sqlReaderTo("LastName").ToString, VbStrConv.ProperCase), _
                StrConv(sqlReaderTo("Address").ToString, VbStrConv.ProperCase), _
                StrConv(sqlReaderTo("Address2").ToString, VbStrConv.ProperCase), _
                StrConv(sqlReaderTo("PostalCode").ToString, VbStrConv.UpperCase))
        End While

        sqlReaderTo.Close()
        'txtAffiliate.Text = strarray
        'Response.Write(strarray)
        Response.Write("User Accounts fixed")

    End Sub

    Private Sub SaveUserProperCase(ByVal strUID As String, ByVal strFirstName As String, _
                ByVal strLastName As String, ByVal strAddress1 As String, ByVal strAddress2 As String, _
                ByVal strPostalCode As String)

        Dim conPubs As SqlConnection
        Dim cmdSelect As SqlCommand
        Dim strSQL As String

        strSQL = "Update members set " & _
                "firstname = @parFirstName, lastname = @parLastName, " & _
                "Address = @parAddress1, Address2 = @parAddress2, PostalCode = @parPostalCode " & _
                " where Member_ID = @parUID"

        conPubs = New SqlConnection(ConfigurationManager.AppSettings.Item("SQLConnectionString"))
        cmdSelect = New SqlCommand(strSQL, conPubs)

        cmdSelect.Parameters.Add("@parUID", SqlDbType.Int).Value = strUID
        cmdSelect.Parameters.Add("@parFirstName", SqlDbType.NVarChar).Value = strFirstName
        cmdSelect.Parameters.Add("@parLastName", SqlDbType.NVarChar).Value = strLastName
        cmdSelect.Parameters.Add("@parAddress1", SqlDbType.NVarChar).Value = strAddress1
        cmdSelect.Parameters.Add("@parAddress2", SqlDbType.NVarChar).Value = strAddress2
        cmdSelect.Parameters.Add("@parPostalCode", SqlDbType.NVarChar).Value = strPostalCode

        conPubs.Open()
        cmdSelect.ExecuteNonQuery()
        conPubs.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        FixUsernameCase()

        'this will check if the email address is correct
        '        Imports System.Text.RegularExpressions

        '        '...
        '        'somewhere in your method
        '        'iterating your recordset one record by one record

        'Dim str As String = 'get your e-mail from the record you got in the prvious step
        '        Dim myRegex As Regex = New Regex("^[\w-]+(?:\.[\w-]+)*@(?:[\w-]+\.)+[a-zA-Z]{2,7}$")
        '        Dim status As Boolean = myRegex.IsMatch(Str)
    End Sub

    Private Sub btnReturn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReturn.Click
        Response.Redirect("default.aspx")
    End Sub
End Class
